# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
from scrapy.pipelines.images import ImagesPipeline
from scrapy import Request
import pymysql
from umeituku.settings import MYSQL


class UmeitukuPipeline:
    def process_item(self, item, spider):
        return item


class PicSavePipeline(ImagesPipeline):  # 自定义管道继承scrapyImagesPipeline进行自动下载图片，需要重写三个方法
    # 想要使用ImagesPipeline需要在settings单独配置文件夹用来保存文件

    def get_media_requests(self, item, info):  # 负责下载文件的
        # 发送请求访问pic_src，导入scrapy.Request
        return Request(item['pic_src'])  # 直接返回一个请求即可

    def file_path(self, request, response=None, info=None, *, item=None):
        # 文件下载寻访路径
        index = "." + request.url.split(".")[-1]  # request.url可以直接获取请求的url
        return f"图片/{item['name'] + index}"  # 图片/XXX

    def item_completed(self, results, item, info):  # 返回文件的详细信息
        print(results)  # 直接打印保存的文件结果
        # [(True, {'url': 'http://kr.shanghai-jiuxin.com/file/2020/0609/4ae39fe6beab0e539357786c2b0a4a13.jpg', 'path': '图片/小清新养眼风景高清桌面壁纸.jpg',
        #          'checksum': '0e83ed03364dbb49553244ec31634d38', 'status': 'downloaded'})]


class MySQLPipelins:
    def open_spider(self, spider):  # 在爬虫爬取数据前就打开文件
        self.connection = pymysql.connect(
            host=MYSQL['host'],  # 拿键为host
            port=MYSQL['port'],
            user=MYSQL['user'],
            password=MYSQL['password'],
            database=MYSQL['database']  # 要连接的数据库名字
        )


def close_spider(self, spider):  # 在爬虫结束后就及时关闭文件
    if self.connection:
        self.connection.close()  # 如果有连接就关闭数据库的连接


def process_item(self, item, spider):
    # 存储数据到数据库
    try:
        cursor = self.connection.cursor()  # 创建数据库连接的游标，负责执行SQL语句
        sql = "insert into pic(pic_name, pic_src) values (%s, %s)"
        cursor.execute(sql, (item['name'], item['pic_src']))
        self.connection.commit()
    except:
        self.connection.rollback()  # 如果报错了就回滚
    finally:
        if cursor:
            cursor.close()
    return item
